package com.example.demo1.repository;

import com.example.demo1.entity.Score;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

public interface ScoreRepository extends JpaRepository<Score, Long> {
    List<Score> findByStudentIdOrderByExamDateDesc(Long studentId);
    List<Score> findByStudentIdInOrderByExamDate(List<Long> studentIds);
    @Query("SELECT s FROM Score s WHERE s.studentId IN :studentIds AND s.examDate = " +
           "(SELECT MAX(s2.examDate) FROM Score s2 WHERE s2.studentId = s.studentId)")
    List<Score> findLatestScoresByStudentIds(List<Long> studentIds);
    @Query("SELECT DISTINCT YEAR(s.examDate) FROM Score s ORDER BY YEAR(s.examDate) DESC")
    List<Integer> findDistinctYears();
    @Query("SELECT DISTINCT MONTH(s.examDate) FROM Score s WHERE YEAR(s.examDate) = :year ORDER BY MONTH(s.examDate)")
    List<Integer> findDistinctMonthsByYear(int year);
    
    @Query("SELECT s FROM Score s WHERE s.major = :major AND s.className = :className " +
           "AND YEAR(s.examDate) = :year AND MONTH(s.examDate) = :month")
    List<Score> findByMajorAndClassNameAndYearAndMonth(
            String major, String className, int year, int month);
} 